<?php

class Timesheet_model extends CI_Model {

    function __construct()
    {
        parent::__construct();
    }
    
    /*
     * Returns a person's timesheet for the specified date
     */
	function get_timesheet($email, $date_range) {
		$work_date = null;
		if ( count($date_range) == 1 ) { $work_date = "t.work_date = '".$date_range['date']."'";  }
		else { $work_date = "t.work_date >= '".$date_range['from']."' AND t.work_date <= '".$date_range['to']."'"; }
		
		return $this->db->query("
			SELECT
				a.activity_id,
				a.timesheet_id,
				a.hours_spent,
				p.project_id,
				p.work_order_no,
				c.dts_code AS code,
				sc.subcode,
				p.project_name,
				c.name AS code_name,
				sc.name AS subcode_name,
				t.work_date
			FROM
				activity AS a
				JOIN timesheet AS t ON a.timesheet_id = t.timesheet_id
				JOIN dts_codes AS c ON a.dts_code = c.dts_code
				JOIN subcodes AS sc ON a.subcode = sc.subcode
				LEFT JOIN pj_activities AS pja ON a.activity_id = pja.activity_id
				LEFT JOIN project AS p ON pja.project_id = p.project_id
			WHERE
				t.email = '".$email."' AND
				".$work_date."
			ORDER BY
				project_name, code, subcode
		")->result();
	}
	
	/*
     * 
     */
	function get_timesheet_review_commit_statuses($email, $date_range) {
		$work_date = null;
		if ( count($date_range) == 1 ) { $work_date = "t.work_date = '".$date_range['date']."'";  }
		else { $work_date = "t.work_date >= '".$date_range['from']."' AND t.work_date <= '".$date_range['to']."'"; }
		
		return $this->db->query("
			SELECT
				t.review_status,
				t.commit_status,
				t.work_date
			FROM
				timesheet AS t
			WHERE
				t.email = '".$email."' AND
				".$work_date."
			ORDER BY
				work_date
		")->result();
	}
	
	/*
	 * Returns the [direct] projects, activities and subcodes available to a specified person (based on projects s/he is a part of)
	 */
	function get_possible_activities($email) {
		return $this->db->query("	
			SELECT
				p.work_order_no,
				pm.project_id,
				pc.dts_code AS code,
				pc.subcode AS subcode,
				p.project_name,
				c.name AS code_name,
				sc.name AS subcode_name
			FROM
				project_members AS pm
				JOIN project AS p ON pm.project_id = p.project_id
				JOIN project_config AS pc ON p.project_id = pc.project_id
				JOIN dts_codes AS c ON pc.dts_code = c.dts_code
				JOIN subcodes AS sc ON pc.subcode = sc.subcode
			WHERE
				member = '".$email."'
				AND p.is_active = TRUE
			ORDER BY
				project_name, code_name, subcode_name
		"/*"	
			SELECT
				p.work_order_no,
				gp.project_id,
				pc.dts_code AS code,
				pc.subcode AS subcode,
				p.project_name,
				c.name AS code_name,
				sc.name AS subcode_name
			FROM
				group_members AS gm
				LEFT JOIN group_projects AS gp ON gp.group_id = gm.group_id
				LEFT JOIN project AS p ON gp.project_id = p.project_id
				JOIN project_config AS pc ON p.project_id = pc.project_id
				JOIN dts_codes AS c ON pc.dts_code = c.dts_code
				JOIN subcodes AS sc ON pc.subcode = sc.subcode
			WHERE
				gm.member = '".$email."'
			ORDER BY
				project_name, code_name, subcode_name
		"*/)->result();
	}
	
	/*
	 * Returns all codes and their subcodes
	 */
	function get_all_activities() {
		return $this->db->query("
			SELECT
				c.name AS code_name,
				sc.name AS subcode_name,
				c.dts_code AS code,
				sc.subcode
			FROM
				dts_code_subcodes AS csc
				JOIN dts_codes AS c ON csc.dts_code = c.dts_code
				JOIN subcodes AS sc ON csc.subcode = sc.subcode
			ORDER BY
				code_name, subcode_name		
		")->result();
	}
	
	/*
	 * 
	 */
	function get_timesheet_record($email, $date_range) {
		$work_date = null;
		if ( count($date_range) == 1 ) { $work_date = "t.work_date = '".$date_range['date']."'";  }
		else { $work_date = "t.work_date >= '".$date_range['from']."' AND t.work_date <= '".$date_range['to']."'"; }
		$result = $this->db->query("SELECT t.* FROM timesheet t WHERE t.email = '".$email."' AND ".$work_date." ORDER BY work_date");
		if ( count($date_range) == 1 ) { $result = $result->row(); }
		else { $result = $result->result(); }
		return $result;
	}
	
	/*
	 * 
	 */
	function get_timesheet_activities($timesheet_id) {
		return $this->db->query("SELECT * FROM activity WHERE timesheet_id = '".$timesheet_id."'")->result();
	}
	
	/*
	 * 
	 */
	function create_timesheet($email, $work_date, $regular_hours, $review_status, $commit_status, $creation_date) {
		$this->db->query("
			INSERT INTO
				timesheet (
					email,
					work_date,
					regular_hrs,
					review_status,
					commit_status,
					creation_date
				)
				VALUES (
					'$email',
					'$work_date',
					'$regular_hours',
					'$review_status',
					'$commit_status',
					'$creation_date'
				)
		");
		return $this->db->insert_id();
	}
	
	/*
	 * 
	 */
	function update_timesheet($timesheet_id, $regular_hours, $review_status, $commit_status) {
		$this->db->query("
			UPDATE timesheet SET
				regular_hrs = '".$regular_hours."',
				review_status = '".$review_status."',
				commit_status = '".$commit_status."'
			WHERE timesheet_id = '".$timesheet_id."'
		");
	}
	
	/*
	 * 
	 */
	function create_activities($activities,$timesheet_id,$direct_or_indirect) {
		foreach($activities as $activity) {
			$this->db->query("INSERT INTO activity (timesheet_id, dts_code, subcode, hours_spent) VALUES ('".$timesheet_id."','".$activity['code']."','".$activity['subcode']."','".$activity['hours_spent']."')");
			$activity_id = $this->db->insert_id();
			if ($direct_or_indirect==='direct') { $this->db->query("INSERT INTO pj_activities (project_id, activity_id) VALUES ('".$activity['project_id']."','".$activity_id."')"); }
			elseif ($direct_or_indirect==='indirect') { $this->db->query("INSERT INTO nonpj_activities (activity_id) VALUES ('".$activity_id."')"); }
		}
	}

	/*
	 * 
	 */
	function delete_timesheet_activities($activity_ids) {
		$activity_ids = implode(',',$activity_ids);
		$this->db->query("DELETE FROM pj_activities WHERE activity_id IN (".$activity_ids.")");
		$this->db->query("DELETE FROM nonpj_activities WHERE activity_id IN (".$activity_ids.")");
		$this->db->query("DELETE FROM activity WHERE activity_id IN (".$activity_ids.")");
	}


}

?>